<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

// -----------------------------------------------------------------------------

class MY_Model extends CI_Model
{
	/**
	 *	Insère une nouvelle ligne dans la base de données.
	 */

	public function create($options_echappees = array(), $options_non_echappees = array())
	{
		$retour = "NULL";
		log_message('debug', '### Création avec le modèle '.$this->table.' ###');
		//	Vérification des données à insérer
		if(empty($options_echappees) AND empty($options_non_echappees))
		{
			return $retour;
		}
		
		if( $this->db->set($options_echappees)->set($options_non_echappees, null, false)->insert($this->table)) {
        	$r = $this->db->query("SELECT LAST_INSERT_ID() as id");
        	$ra = $r->result_array();
        	$ra0 = $ra['0'];
        	$retour = $ra0['id'];
        	return $retour;
        }                    
	}
	

	/**
	 *	Récupère des données dans la base de données.
	 */
	public function read($select = '*', $where = array(), $protect = TRUE)
	{
		log_message('debug', '### Lecture avec le modèle '.$this->table.' ###');
		return $this->db->select($select, $protect)
        	            ->from($this->table)
                        ->where($where)
                        ->get();
	}
	
	public function MotDePasse($id, $password) {
		log_message('debug', '### Vérification du mot de passe.');
		$result = $this->db->query('SELECT AES_DECRYPT(`mdp`,\'password\') AS `motdepasse` FROM '.$this->table.' WHERE `id`=\''.$id.'\'');
		$motdepasse = $result->row('motdepasse');
		return ($motdepasse == $password);
	}
	
	/**
	 *	Modifie une ou plusieurs lignes dans la base de données.
	 */
	public function update($where, $options_echappees = array(), $options_non_echappees = array())
	{
		log_message('debug', '### Mise à jour avec le modèle '.$this->table.' ###');

		//	Vérification des données à mettre à jour
		if(empty($options_echappees) AND empty($options_non_echappees))
		{
			return false;
		}
	
		//	Raccourci dans le cas où on sélectionne l'id
		if(is_integer($where)) { $where = array('id' => $where); }
		
		return (bool) $this->db->set($options_echappees)
		                       ->set($options_non_echappees, null, false)
                               ->where($where)
                               ->update($this->table);

	}
	
	/**
	 *	Supprime une ou plusieurs lignes de la base de données.
	 */
	public function delete($where)
	{
		log_message('debug', '### Supression avec le modèle '.$this->table.' ###');
		if(is_integer($where))
		{
			$where = array('id' => $where);
		}
		return (bool) $this->db->where($where)
                               ->delete($this->table);
	}
	/**
	 *	Retourne le nombre de résultats.
	 */
	public function count($champ = array(), $valeur = null) // Si $champ est un array, la variable $valeur sera ignorée par la méthode where()
	{
		$retour = 0;

		$retour = $this->db->where($champ, $valeur)
                            ->from($this->table)
                            ->count_all_results();
                              
        log_message('debug', '### Comptage avec le modèle '.$this->table.' - Nombre retourné : '.$retour.' ###');
        
        return (int) $retour;
	}

	function commandesJqgrid($paramArr) {
		$SQL = "SELECT commande.id as id, DATE_FORMAT(date, '%d/%m/%Y') as datejma, CONCAT(nom, ' ', prenom) as responsable, libelle as statut, montant FROM commande, user, statut  WHERE commande.userId=user.id AND commande.statutId=statut.id order by date";
		$result = $this->db->query($SQL);
		$nb_commandes = $result->num_rows();
		
		log_message('info','### Passage dans commandesJqGrid() => '.$nb_commandes.' commandes extraites. ###');
	
		if($nb_commandes > 0) {
			$commandesliste = $result->result();
			return $commandesliste;
		} else {
			return null;
		}
	}
	
	function lignesJqgrid($paramArr) {
		$SQL = "SELECT article.id as id, article.code as code, article.libelle as libelle, article.poids as poids,  l.nombre as nombre, article.pu as pu, l.montant as montant, article.categorieId as categorieId, c.libelle as catlibelle
		        FROM article
				LEFT JOIN (SELECT * FROM lignescde WHERE userId=".$this->session->userdata('id')." AND commandeId=".$this->session->userdata('NCommande').") l
		        ON article.id = l.articleId
		        LEFT JOIN (SELECT * FROM categorie) c
		        ON article.categorieId = c.id
		        ORDER BY article.categorieId, article.code";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function allLignesJqgrid($paramArr) {
		$SQL = "SELECT article.id as id, article.code as code, article.libelle as libelle, article.poids as poids, 
				SUM(l.nombre) as nombre, article.pu as pu, SUM(l.montant) as montant 
		        FROM article
				LEFT JOIN (SELECT * FROM lignescde WHERE commandeId=".$this->session->userdata('NCommande').") l
		        ON article.id = l.articleId
		        GROUP BY article.id";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'allLignes\' => '.$nb_lignes.' lignes extraites. ###');
		
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function usersCommandeJqgrid($paramArr) {
		$SQL = "SELECT distinct user.id as id, CONCAT(prenom, ' ', nom) as nomprenom FROM user,lignescde WHERE user.id = lignescde.userId AND lignescde.commandeId = ".$this->session->userdata('NCommande');
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'usersCommande\' => '.$nb_lignes.' users extraits. ###',0);
	
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function userLignesJqgrid() {
		$SQL = "SELECT article.id as id, article.code as code, article.libelle as libelle, article.poids as poids,  l.nombre as nombre, article.pu as pu, l.montant as montant 
		        FROM article
				LEFT JOIN (SELECT * FROM lignescde WHERE userId=".$this->session->userdata('user_requete')." AND commandeId=".$this->session->userdata('NCommande').") l
		        ON article.id = l.articleId";

		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'usersLignes\' - User '.$this->session->userdata('user_requete').' => '.$nb_lignes.' lignes extraites.');
		
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function articlesJqgrid($paramArr) {		
		$SQL = "SELECT article.id as id, code, DATE_FORMAT(validite, '%d/%m/%Y') as validite, article.libelle as libelle, poids, pu, categorieId, categorie.libelle as categorie
				FROM article, categorie
		        WHERE article.categorieId = categorie.id
		        ORDER BY categorieId,code ASC";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'articles\' => '.$nb_lignes.' articles extraits.',0);
	
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function usersJqgrid($paramArr) {
		$SQL = "SELECT id, nom, prenom, login, email, admin from user";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'users\' => '.$nb_lignes.' users extraits. ###',0);
	
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}

	function usersNomPrenomJqgrid($paramArr) {
		$SQL = "SELECT id, CONCAT(prenom, ' ', nom) as nomprenom FROM user";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'usersNomPrenom\' => '.$nb_lignes.' users extraits. ###',0);
	
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function statutsJqgrid($paramArr) {
		$SQL = "SELECT id, libelle, suivantId from statut";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'statuts\' => '.$nb_lignes.' statuts extraits. ###');
	
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function paramsJqgrid($paramArr) {
		$SQL = "SELECT id, parametre, valeur from parametre";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'paramètres\' => '.$nb_lignes.' paramètres extraits. ###');
	
		if($nb_lignes > 0) {
			$lignesliste = $result->result();
			return $lignesliste;
		} else {
			return null;
		}
	}
	
	function totalCommande($numc, $numu = null) { // Numéro de commande et numéro de user (si null, ne sera pas dans where)
		$whereUser = ($numu != null) ? " AND userId ='".$numu."'" : "";
		$SQL = "SELECT SUM(montant) as montant FROM lignescde WHERE commandeId='".$numc."'".$whereUser;
		log_message('debug', 'Requête total commande : '.$SQL);
		$resultat = $this->db->query($SQL);
		$total = $resultat->result_array();
		return $total['0'];
	}
	
	function categoriesJqgrid($paramArr) {		
		$SQL = "SELECT id, libelle from categorie";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'categories\' => '.$nb_lignes.' categories extraites.',0);
	
		if($nb_lignes > 0) {
			$categliste = $result->result();
			return $categliste;
		} else {
			return null;
		}
	}
	
	function prixJqgrid($paramArr) {		
		$SQL = "SELECT datefin, prix from prix where articleId='".$this->session->userdata('NArticle')."'";
		$result = $this->db->query($SQL);
		$nb_lignes = $result->num_rows();
		
		log_message('info', '### Chargement de la grille \'prix\' => '.$nb_lignes.' prix extraits.',0);
	
		if($nb_lignes > 0) {
			$prixliste = $result->result();
			return $prixliste;
		} else {
			return null;
		}
	}
	
	function recapCommandePourExcel($NCommande) {
		$SQL = "SELECT article.code as code, SUM(lignescde.nombre) as nombre
		FROM article,lignescde WHERE article.id=lignescde.articleId AND lignescde.commandeId='".$NCommande."'
		GROUP BY article.code";
		
		$resultat = $this->db->query($SQL);
		$CommandeExcel = $resultat->result_array();
		return $CommandeExcel;
	}
	
	function commandeUser ($NCommande, $NUser) {
		$SQL = "SELECT article.libelle as libelle, article.pu as pu, lignescde.nombre as nombre, lignescde.montant as montant FROM lignescde, article
				where commandeId='".$NCommande."' and userId='".$NUser."'
				and lignescde.articleId=article.id";
		
		$resultat = $this->db->query($SQL);
		$ListeArticles = $resultat->result_array();
		return $ListeArticles;		
	}
}

/* End of file MY_Model.php */
/* Location: ./system/application/core/MY_Model.php */
